package com.medium.house.utils.excel;

import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.ss.util.RegionUtil;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.math.BigDecimal;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;
import java.util.Map;

public class ExcelTools {
    private String name;
    private String[] hreads;
    private String[] fields;
    private Map<String, Double> totals;
    private List data;
    private HSSFWorkbook wb;
    private HSSFSheet sheet;

    /**
     * 导出 创建的excel
     *
     * @param request
     * @param response
     * @throws Exception
     */
    public void printExcel(HttpServletRequest request, HttpServletResponse response) throws Exception {
        if (StringUtils.isBlank(this.getName())) {
            throw new RuntimeException("shell name is null ");
        }
        wb = new HSSFWorkbook();
        sheet = wb.createSheet(name);
        sheet.setFitToPage(true);
        sheet.setHorizontallyCenter(true);
        sheet.setAutobreaks(true);
        sheet.setDefaultColumnWidth(13);

//        this.settleTitleRow(sheet);
        ExcelStype.priceStyle = null;
        ExcelStype.numberStyle = null;
        ExcelStype.strStyle = null;
        this.settleHreadsRow(sheet);
        this.settleSheetData(sheet);
        this.settleTotalRow(sheet);

        try {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
            String fileName = this.getName();
            fileName = java.net.URLEncoder.encode(fileName, "UTF-8") + "-" + sdf.format(new Date());
            response.setContentType("application/vnd.ms-excel");

            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
            OutputStream ouputStream = response.getOutputStream();
            wb.write(ouputStream);
            ouputStream.flush();
            ouputStream.close();

        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    /**
     * 创建excel 的数据
     *
     * @param sheet
     * @throws Exception
     */
    private void settleSheetData(HSSFSheet sheet) throws Exception {
        for (int i = 0; i < data.size(); i++) {
            int startId = sheet.getLastRowNum() + 1;
            HSSFRow row = sheet.createRow(startId);
            for (int j = 0; j < fields.length; j++) {
                settleRowCell(row, j, i);
            }
        }
    }

    /**
     * 生成 Row Cell数据
     *
     * @param row
     * @param index
     * @param num
     * @throws Exception
     */
    private void settleRowCell(Row row, int index, int num) throws Exception {
        Cell cell = row.createCell(index);
        String field = fields[index];
        if ("0xx0".equals(field)) {
            cell.setCellStyle(ExcelStype.getStrStyle(wb));
            cell.setCellValue(num + 1);
            cell.setCellType(0);
        } else {
            Object object = MethodTool.getValue(data.get(num), MethodTool.getMethodNameByGet(fields[index]));
            if (object == null) {
                cell.setCellType(3);
                cell.setCellStyle(ExcelStype.getNumberStyle(wb));

            } else if ((object instanceof Integer)) {
                cell.setCellType(0);
                cell.setCellStyle(ExcelStype.getNumberStyle(wb));
                cell.setCellValue(Integer.parseInt(object.toString()));
            } else if (object instanceof Date) {
                cell.setCellType(1);
                Date date = (Date) object;
                SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                cell.setCellStyle(ExcelStype.getStrStyle(wb));
                cell.setCellValue(df.format(date));
            } else if (object instanceof Float) {
                cell.setCellType(0);
                cell.setCellStyle(ExcelStype.getNumberStyle(wb));
                cell.setCellValue(Double.parseDouble(object.toString()));
            } else if (object instanceof BigDecimal) {
                cell.setCellType(0);
                cell.setCellStyle(ExcelStype.getPriceStyle(wb));
                DecimalFormat df1 = new DecimalFormat("####.00");
                cell.setCellValue(df1.parse(object.toString()).doubleValue());
            } else {
                cell.setCellType(1);
                cell.setCellStyle(ExcelStype.getStrStyle(wb));
                cell.setCellValue(object.toString());
            }
            if (totals.containsKey(field)) {
                totals.put(field, totals.get(field) + Double.valueOf(object.toString()));
            }
        }
    }

    /**
     * 创建excel的 title
     *
     * @param sheet
     */
    private void settleTitleRow(HSSFSheet sheet) {
        if (this.getHreads() == null || this.getHreads().length == 0) {
            throw new RuntimeException(" hreads is null  ");
        }
        HSSFRow titleRow = sheet.createRow(0);
        titleRow.setHeightInPoints(35);
        HSSFCell cell = titleRow.createCell(0);
        cell.setCellType(1);
        cell.setCellStyle(ExcelStype.getTitleStyle(wb));
        cell.setCellValue(name);

        CellRangeAddress region = new CellRangeAddress(0, 0, 0, hreads.length - 1);
        sheet.addMergedRegion(region);
        RegionUtil.setBorderTop(1, region, sheet, wb);
        RegionUtil.setBorderRight(1, region, sheet, wb);
        RegionUtil.setBorderBottom(1, region, sheet, wb);
        RegionUtil.setBorderLeft(1, region, sheet, wb);
    }

    /**
     * 生成excel 的 列名
     *
     * @param sheet
     */
    private void settleHreadsRow(HSSFSheet sheet) {
        if (this.getHreads() == null || this.getHreads().length == 0) {
            throw new RuntimeException(" hreads is null  ");
        }
        int rowId = sheet.getLastRowNum();
        HSSFRow headRow = sheet.createRow(rowId);
        headRow.setHeightInPoints(20);
        for (int i = 0; i < hreads.length; i++) {
            HSSFCell cell = headRow.createCell(i);
            cell.setCellStyle(ExcelStype.getHeaderStyle(wb));
            cell.setCellType(1);
            cell.setCellValue(hreads[i]);
            sheet.setColumnWidth(i, 5100);
        }
    }

    /**
     * 生成 excel的统计行
     *
     * @param sheet
     * @throws Exception
     */
    private void settleTotalRow(HSSFSheet sheet) throws Exception {
        if (this.totals != null && this.totals.size() > 0) {
            int rowId = sheet.getLastRowNum() + 1;
            HSSFRow totalRow = sheet.createRow(rowId);
            totalRow.setHeightInPoints(25);
            HSSFCell ncell = totalRow.createCell(0);
            ncell.setCellStyle(ExcelStype.getStrStyle(wb));
            ncell.setCellType(1);
            ncell.setCellValue("汇总");
            for (int i = 1; i < fields.length; i++) {
                if (totals.containsKey(fields[i])) {
                    HSSFCell cell = totalRow.createCell(i);
                    cell.setCellStyle(ExcelStype.getNumberStyle(wb));
                    cell.setCellValue(totals.get(fields[i]));
                    cell.setCellType(0);
                } else {
                    HSSFCell cell = totalRow.createCell(i);
                    cell.setCellStyle(ExcelStype.getStrStyle(wb));
                    cell.setCellValue("");
                    cell.setCellType(1);
                }
            }
        }
    }


    public String getName() {
        return name;
    }

    public void setName(String name) {
        this.name = name;
    }

    public String[] getHreads() {
        return hreads;
    }

    public void setHreads(String[] hreads) {
        String[] newHreads = new String[hreads.length + 1];
        newHreads[0] = "编号";
        for (int i = 0; i < hreads.length; i++) {
            newHreads[i + 1] = hreads[i];
        }
        this.hreads = newHreads;
    }

    public String[] getFields() {
        return fields;
    }

    public void setFields(String[] fields) {
        String[] newFields = new String[fields.length + 1];
        newFields[0] = "0xx0";
        for (int i = 0; i < fields.length; i++) {
            newFields[i + 1] = fields[i];
        }
        this.fields = newFields;
    }

    public Map<String, Double> getTotals() {
        return totals;
    }

    public void setTotals(Map<String, Double> totals) {
        this.totals = totals;
    }

    public List getData() {
        return data;
    }

    public void setData(List data) {
        this.data = data;
    }

    public void printExcelData2Map(HttpServletRequest request, HttpServletResponse response) throws Exception {
        if (StringUtils.isBlank(this.getName())) {
            throw new RuntimeException("shell name is null ");
        }
        wb = new HSSFWorkbook();
        sheet = wb.createSheet(name);
        sheet.setFitToPage(true);
        sheet.setHorizontallyCenter(true);
        sheet.setAutobreaks(true);
        sheet.setDefaultColumnWidth(13);

        ExcelStype.priceStyle = null;
        ExcelStype.numberStyle = null;
        ExcelStype.strStyle = null;
        this.settleHreadsRow(sheet);
        this.settleSheetData2Map(sheet);
        this.settleTotalRow(sheet);

        try {
            SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
            String fileName = this.getName();
            fileName = java.net.URLEncoder.encode(fileName, "UTF-8") + "-" + sdf.format(new Date());
            response.setContentType("application/vnd.ms-excel");
            response.setHeader("Content-disposition", "attachment;filename=" + fileName + ".xls");
            OutputStream ouputStream = response.getOutputStream();
            wb.write(ouputStream);
            ouputStream.flush();
            ouputStream.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    private void settleSheetData2Map(HSSFSheet sheet) throws Exception {
        for (int i = 0; i < data.size(); i++) {
            int startId = sheet.getLastRowNum() + 1;
            HSSFRow row = sheet.createRow(startId);
            for (int j = 0; j < fields.length; j++) {
                settleRowCell2Map(row, j, i);
            }
        }
    }

    private void settleRowCell2Map(Row row, int index, int num) throws Exception {
        Cell cell = row.createCell(index);
        String field = fields[index];
        if ("0xx0".equals(field)) {
            cell.setCellStyle(ExcelStype.getStrStyle(wb));
            cell.setCellValue(num + 1);
            cell.setCellType(0);
        } else {
            Map<String, Object> results = (Map<String, Object>) data.get(num);
            Object object = results.get(fields[index]);
            if (object == null) {
                cell.setCellType(3);
                cell.setCellStyle(ExcelStype.getNumberStyle(wb));
            } else if ((object instanceof Integer)) {
                cell.setCellType(0);
                cell.setCellStyle(ExcelStype.getNumberStyle(wb));
                cell.setCellValue(Integer.parseInt(object.toString()));
            } else if (object instanceof Date) {
                cell.setCellType(1);
                Date date = (Date) object;
                SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                cell.setCellStyle(ExcelStype.getStrStyle(wb));
                cell.setCellValue(df.format(date));
            } else if (object instanceof Float) {
                cell.setCellType(0);
                cell.setCellStyle(ExcelStype.getNumberStyle(wb));
                cell.setCellValue(Double.parseDouble(object.toString()));
            } else if (object instanceof BigDecimal) {
                cell.setCellType(0);
                cell.setCellStyle(ExcelStype.getPriceStyle(wb));
                DecimalFormat df1 = new DecimalFormat("####.00");
                cell.setCellValue(df1.parse(object.toString()).doubleValue());
            } else {
                cell.setCellType(1);
                cell.setCellStyle(ExcelStype.getStrStyle(wb));
                cell.setCellValue(object.toString());
            }
            if (totals.containsKey(field)) {
                totals.put(field, totals.get(field) + Double.valueOf(object.toString()));
            }
        }
    }
}
